

/*
	Base Data Access Layer.
	(C) Lehavi Solutions	

*/
	
#region using	
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
#endregion


namespace DAL
{	
	
	public partial class Users
	{

		#region Basic CRUD
		// Create / Update / Delete
		protected internal static int Insert(BO.User user)
		{
			var cmd =  @"
				insert into [Users]
					([GoogleID], [Token], [ClarizenSession], [UserName], [ClarizenUserID], [GoogleOauthToken], [GoogleOauthSecret])
				values
					(@GoogleID, @Token, @ClarizenSession, @UserName, @ClarizenUserID, @GoogleOauthToken, @GoogleOauthSecret);
				
				select scope_identity();
				";
				
			var parameters = new SqlParameter[7]; 
			
			if (user.GoogleID == null)
				parameters[0] = new SqlParameter("GoogleID",DBNull.Value);
			else
				parameters[0] = new SqlParameter("GoogleID",user.GoogleID);
			if (user.Token == null)
				parameters[1] = new SqlParameter("Token",DBNull.Value);
			else
				parameters[1] = new SqlParameter("Token",user.Token);
			if (user.ClarizenSession == null)
				parameters[2] = new SqlParameter("ClarizenSession",DBNull.Value);
			else
				parameters[2] = new SqlParameter("ClarizenSession",user.ClarizenSession);
			if (user.UserName == null)
				parameters[3] = new SqlParameter("UserName",DBNull.Value);
			else
				parameters[3] = new SqlParameter("UserName",user.UserName);
			if (user.ClarizenUserID == null)
				parameters[4] = new SqlParameter("ClarizenUserID",DBNull.Value);
			else
				parameters[4] = new SqlParameter("ClarizenUserID",user.ClarizenUserID);
			if (user.GoogleOauthToken == null)
				parameters[5] = new SqlParameter("GoogleOauthToken",DBNull.Value);
			else
				parameters[5] = new SqlParameter("GoogleOauthToken",user.GoogleOauthToken);
			if (user.GoogleOauthSecret == null)
				parameters[6] = new SqlParameter("GoogleOauthSecret",DBNull.Value);
			else
				parameters[6] = new SqlParameter("GoogleOauthSecret",user.GoogleOauthSecret);
			
			return Convert.ToInt32(DBHelper.GetScalar(cmd, CommandType.Text, parameters));
		}
		
		protected internal static void Update(BO.User user)
		{
			var cmd = @"
				update [Users] set
					GoogleID = @GoogleID, Token = @Token, ClarizenSession = @ClarizenSession, UserName = @UserName, ClarizenUserID = @ClarizenUserID, GoogleOauthToken = @GoogleOauthToken, GoogleOauthSecret = @GoogleOauthSecret
				where ID = @ID
				";
				
			var parameters = new SqlParameter[8];
			parameters[0] = new SqlParameter("ID",user.ID );
			if (user.GoogleID == null)
				parameters[1] = new SqlParameter("GoogleID",DBNull.Value);
			else
				parameters[1] = new SqlParameter("GoogleID",user.GoogleID);
			if (user.Token == null)
				parameters[2] = new SqlParameter("Token",DBNull.Value);
			else
				parameters[2] = new SqlParameter("Token",user.Token);
			if (user.ClarizenSession == null)
				parameters[3] = new SqlParameter("ClarizenSession",DBNull.Value);
			else
				parameters[3] = new SqlParameter("ClarizenSession",user.ClarizenSession);
			if (user.UserName == null)
				parameters[4] = new SqlParameter("UserName",DBNull.Value);
			else
				parameters[4] = new SqlParameter("UserName",user.UserName);
			if (user.ClarizenUserID == null)
				parameters[5] = new SqlParameter("ClarizenUserID",DBNull.Value);
			else
				parameters[5] = new SqlParameter("ClarizenUserID",user.ClarizenUserID);
			if (user.GoogleOauthToken == null)
				parameters[6] = new SqlParameter("GoogleOauthToken",DBNull.Value);
			else
				parameters[6] = new SqlParameter("GoogleOauthToken",user.GoogleOauthToken);
			if (user.GoogleOauthSecret == null)
				parameters[7] = new SqlParameter("GoogleOauthSecret",DBNull.Value);
			else
				parameters[7] = new SqlParameter("GoogleOauthSecret",user.GoogleOauthSecret);
			DBHelper.ExecuteNonQuery(cmd, CommandType.Text, parameters);
		}
		
		protected internal static void Delete(object ID)
		{
			var cmd = "delete [Users] where ID = @ID";
			
			DBHelper.ExecuteNonQuery(cmd, CommandType.Text, new SqlParameter("ID", ID));
		}

		#endregion 
			
		internal static DataTable Select(string cmd, SqlParameter[] parameters)
		{
			return DBHelper.GetTable(cmd, parameters);
		}
		
		internal static int Count(string cmd, SqlParameter[] parameters)
		{
			return Convert.ToInt32(DBHelper.GetScalar(cmd, parameters));
		}
		
		internal static void SetValue(object id, string columnName, object value)
		{
			var cmd = string.Format("update [Users] set [{0}] = @Value where ID = @ID", columnName);
			
			DBHelper.ExecuteNonQuery(cmd, new SqlParameter("Value", value ?? DBNull.Value), new SqlParameter("ID", id));
		}
		
		internal static void SafeDetach(object fkID, string columnName)
		{
			var cmd = string.Format("update [Users] set [{0}] = null where [{0}] = @FkID", columnName);

			DBHelper.ExecuteNonQuery(cmd, new SqlParameter("FkID", fkID));
		}
	
	}
			
}



